package cn.yi.newsys.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.yi.newsys.dao.NewsDao;
import cn.yi.newsys.pojo.News;
import cn.yi.newsys.pojo.PageBean;
import cn.yi.newsys.utils.BaseDao;

public class NewsDaoImpl extends BaseDao implements NewsDao{

	@Override
	public List<News> getIndexNews() {
		String sql = "SELECT nid,ntitle FROM news_detail ORDER BY ncreateDate DESC LIMIT 14" ;
		List<News> list = new ArrayList<News>();
		News news = null ;
		ResultSet rs = null ;
		// 执行 SQL 语句
		try{
			rs = this.executeQuery(sql, null) ;
			while(rs != null && rs.next()){
				news = new News() ;
				news.setNid(rs.getInt("nid")) ;
				news.setTittle(rs.getString("ntitle")) ;
				list.add(news) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return list ;
	}

	@Override
	public List<News> getAllInlandNews() {
		String sql = "SELECT nid,ntitle FROM news_detail WHERE ntid=1 ORDER BY ncreateDate DESC LIMIT 10" ;
		List<News> list = new ArrayList<News>();
		News news = null ;
		ResultSet rs = null ;
		// 执行 SQL 语句
		try{
			rs = this.executeQuery(sql, null) ;
			while(rs != null && rs.next()){
				news = new News() ;
				news.setTittle(rs.getString("ntitle")) ;
				list.add(news) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close() ;
		}
		return list ;
	}

	@Override
	public News getNewsByID(int id) {
		News n = null ; 
		String sql = "SELECT nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary FROM news_detail WHERE nid=?" ;
		Object objs [] = new Object []{id} ;
		ResultSet rs = this.executeQuery(sql, objs) ;
		try {
			if(rs != null && rs.next()){
				n = new News() ;
				n.setAuthor(rs.getString("nauthor")) ;
				n.setContent(rs.getString("ncontent")) ;
				n.setDateTime(rs.getDate("ncreateDate")) ;
				n.setImgPath(rs.getString("npicPath")) ;
				n.setModDate(rs.getDate("nmodifyDate")) ;
				n.setNid(rs.getInt("nid")) ;
				n.setNtid(rs.getInt("ntid")) ;
				n.setSummary(rs.getString("nsummary")) ;
				n.setTittle(rs.getString("ntitle")) ;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return n ;
	}

	@Override
	public List<News> getSerachNews(String str) {
		List<News> list = new ArrayList<News>() ;
		News news = null ;
		String sql = "SELECT nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary FROM news_detail WHERE ntitle LIKE ? OR ncontent LIKE ? OR nsummary LIKE ?" ;
		Object objs [] = new Object[]{"%"+str+"%","%"+str+"%","%"+str+"%"} ;
		ResultSet rs = this.executeQuery(sql, objs) ;
		try {
			while(rs != null && rs.next()){
				news = new News() ;
				news.setNid(rs.getInt("nid")) ;
				news.setTittle(rs.getString("ntitle")) ;
				list.add(news) ;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list ;
	}

	@Override
	public List<News> getAllNews() {
		News n = null ;
		List<News> list = new ArrayList<News>() ;
		String sql = "SELECT nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary FROM news_detail" ;
		ResultSet rs = this.executeQuery(sql, null) ;
		try {
			while(rs != null && rs.next()){
				n = new News() ;
				n.setAuthor(rs.getString("nauthor")) ;
				n.setContent(rs.getString("ncontent")) ;
				n.setDateTime(rs.getDate("ncreateDate")) ;
				n.setImgPath(rs.getString("npicPath")) ;
				n.setModDate(rs.getDate("nmodifyDate")) ;
				n.setNid(rs.getInt("nid")) ;
				n.setNtid(rs.getInt("ntid")) ;
				n.setSummary(rs.getString("nsummary")) ;
				n.setTittle(rs.getString("ntitle")) ;
				list.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list ;
	}

	@Override
	public int addNews(News news) {
		String sql = "INSERT INTO news_detail(ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary) VALUES(?,?,?,?,?,?,?,?)" ;
		Object objs [] = new Object []{news.getNtid(),news.getTittle(),news.getAuthor(),news.getDateTime(),news.getImgPath(),news.getContent(),news.getModDate(),news.getSummary()};
		int row = 0 ;
		try{
			row = this.executeUpdate(sql, objs) ;
		}finally{
			this.close() ;
		}
		return row ;
	}

	@Override
	public News getNewsByName(String title) {
		News n = null ; 
		String sql = "SELECT nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary FROM news_detail WHERE ntitle=?" ;
		Object objs [] = new Object []{title} ;
		ResultSet rs = this.executeQuery(sql, objs) ;
		try {
			if(rs != null && rs.next()){
				n = new News() ;
				n.setAuthor(rs.getString("nauthor")) ;
				n.setContent(rs.getString("ncontent")) ;
				n.setDateTime(rs.getDate("ncreateDate")) ;
				n.setImgPath(rs.getString("npicPath")) ;
				n.setModDate(rs.getDate("nmodifyDate")) ;
				n.setNid(rs.getInt("nid")) ;
				n.setNtid(rs.getInt("ntid")) ;
				n.setSummary(rs.getString("nsummary")) ;
				n.setTittle(rs.getString("ntitle")) ;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return n ;
	}

	@Override
	public List<News> getAllNews(int offset , int pageSize , String searchTitle , int searchTid) {
		News n = null ;
		List temp = new ArrayList() ;
		List<News> list = new ArrayList<News>() ;
		StringBuffer sql = new StringBuffer() ;
		sql.append("SELECT nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary ") ;
		sql.append("FROM news_detail WHERE 1=1 ") ;	
		if(searchTitle != null && !searchTitle.equals("")){
			sql.append("AND ntitle LIKE ? ") ;
			temp.add("%"+searchTitle+"%") ;
		}
		if(searchTid > 0){
			sql.append("AND ntid=? ") ;
			temp.add(searchTid) ;
		}
		sql.append("ORDER BY ncreateDate DESC LIMIT ?,? ") ;
		temp.add(offset) ;
		temp.add(pageSize) ;
		
		Object objs [] = temp.toArray() ;
		ResultSet rs = this.executeQuery(sql.toString(), objs) ;
		try {
			while(rs != null && rs.next()){
				
				n = new News() ;
				n.setAuthor(rs.getString("nauthor")) ;
				n.setContent(rs.getString("ncontent")) ;
				n.setDateTime(rs.getDate("ncreateDate")) ;
				n.setImgPath(rs.getString("npicPath")) ;
				n.setModDate(rs.getDate("nmodifyDate")) ;
				n.setNid(rs.getInt("nid")) ;
				n.setNtid(rs.getInt("ntid")) ;
				n.setSummary(rs.getString("nsummary")) ;
				n.setTittle(rs.getString("ntitle")) ;
				list.add(n);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}              
		return list ;
	}

	@Override
	public int queryTotalRecord(String searchTitle , int seachTid) throws Exception {
		int row = 0 ;
		StringBuffer sql = new StringBuffer() ;
		List list = new ArrayList() ;
		sql.append("SELECT COUNT(1) FROM news_detail WHERE 1=1 ") ;
		if( searchTitle != null && !searchTitle.equals("")){
			sql.append("AND ntitle LIKE ? ") ;
			list.add("%"+searchTitle+"%");
		}
		if( seachTid > 0){
			sql.append("AND ntid=? ") ;
			list.add(seachTid) ;
		}
		Object objs [] = list.toArray() ;
		ResultSet rs = this.executeQuery(sql.toString(), objs) ;
		if(rs != null && rs.next()){
			row = rs.getInt(1) ;
		}
		this.close() ;
		return row ;
	}

	@Override
	public int delNews(int newsId) {
		int row = 0 ;
		String sql = "SELECT cid FROM news_comments WHERE cnid=?" ;
		// 设置参数
		Object objs [] = new Object[]{newsId} ;
		// 执行 SQL 语句
		ResultSet rs = this.executeQuery(sql, objs) ;
		try {
			if(rs.next()){
				sql = "DELETE FROM news_comments WHERE nid=?" ;
				// 删除评论
				this.executeUpdate(sql, objs) ;
			}
			sql = "DELETE FROM news_detail WHERE nid=?" ;
			// 删除新闻
			row = this.executeUpdate(sql, objs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row ;
	}

	
}
